package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;

import co.gov.medellin.ssti.sub.model.dao.SecretariaDAO;
import co.gov.medellin.ssti.sub.model.vo.AreaVO;

public class SecretariaDAOImpl2 extends NamedParameterJdbcDaoSupport implements
		SecretariaDAO {

	// **************************************
	// QUERYS
	// **************************************
	private static final String SELECT_TODAS_SECRETARIAS = "SELECT AREA.CODIGO CODIGO, AREA.NOMBRE NOMBRE "
			+ "FROM TCS_BEN_AREA AREA WHERE AREA.AREA_CODIGO IS NULL "
			+ "ORDER BY AREA.NOMBRE";

	private static final String SELECT_BY_USER = "SELECT A.CODIGO CODIGO, A.NOMBRE NOMBRE "
			+ " FROM TCS_BEN_AREA A WHERE A.CODIGO = ANY(SELECT PRO.AREA_CODIGO "
			+ "FROM TCS_BEN_PROGRAMA PRO WHERE (PRO.VIGENCIA = 'A' OR SYSDATE BETWEEN PRO.DESDE_VIGENCIA AND  PRO.HASTA_VIGENCIA) "
			+ "AND PRO.USU_CODIGO = :strCodeUsu "
			+ // PARAMETRO
			"UNION SELECT AREA.AREA_CODIGO "
			+ "FROM TCS_BEN_SUBPROGRAMA SUB, TCS_BEN_AREA AREA "
			+ "WHERE SUB.AREA_CODIGO = AREA.CODIGO "
			+ "AND (SUB.VIGENCIA = 'A' OR SYSDATE BETWEEN SUB.DESDE_VIGENCIA AND  SUB.HASTA_VIGENCIA) "
			+ "AND SUB.USU_CODIGO = :strCodeUsu "
			+ // PARAMETRO
			"UNION SELECT CAMPO_N1 FROM PAOYER.TCS_USUARIO "
			+ "WHERE CAMPO_N1 IS NOT NULL AND CODIGO = :strCodeUsu "
			+ "UNION  SELECT AREA.AREA_CODIGO "
			+ // PARAMETRO
			"FROM TCS_BEN_SUBPROGRAMA_USUARIO SUBUS, TCS_BEN_SUBPROGRAMA SUB, TCS_BEN_AREA AREA "
			+ "WHERE SUBUS.SBPR_CODIGO = SUB.CODIGO AND SUB.AREA_CODIGO = AREA.CODIGO "
			+ "AND (SUB.VIGENCIA = 'A' OR SYSDATE BETWEEN SUB.DESDE_VIGENCIA AND  SUB.HASTA_VIGENCIA) "
			+ "AND SUBUS.USU_CODIGO = :strCodeUsu ) ORDER BY A.NOMBRE"; // PARAMETRO

	public List<AreaVO> loadAllSecretarias() {
		List<AreaVO> secretarias = getNamedParameterJdbcTemplate()
				.getJdbcOperations().query(SELECT_TODAS_SECRETARIAS,
						new RowMapper<AreaVO>() {

							public AreaVO mapRow(ResultSet rst, int rowNum)
									throws SQLException {
								AreaVO vo = new AreaVO();
								vo.setCodearea(rst.getString("CODIGO"));
								vo.setNombre(rst.getString("NOMBRE"));
								return vo;
							}
						});
		return secretarias;
	}

	public List<AreaVO> loadSecretariasByUser(String strCodeUsu) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		namedParameters.put("strCodeUsu", strCodeUsu);
		List<AreaVO> secretarias = getNamedParameterJdbcTemplate().query(
				SELECT_BY_USER, new HashMap<String, Object>(),
				new RowMapper<AreaVO>() {

					public AreaVO mapRow(ResultSet rst, int rowNum)
							throws SQLException {
						AreaVO vo = new AreaVO();
						vo.setCodearea(rst.getString("CODIGO"));
						vo.setNombre(rst.getString("NOMBRE"));
						return vo;
					}
				});
		return secretarias;
	}

}
